#!/bin/bash

if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d '-1 days' +%F`
fi

ads_goods_price_product="
INSERT OVERWRITE TABLE jtp_goods_warehouse.ads_goods_price_product
SELECT
    product_id
    ,CAST(price_star AS BIGINT)
    ,price
    ,CAST(relation_uv AS BIGINT)
    ,pay_buyer_count
    ,round(pay_buyer_count / relation_uv,4) AS pay_rate
FROM jtp_goods_warehouse.dwd_goods_info
WHERE dt = '${data_date}'
AND price_star IS NOT NULL
GROUP BY product_id, price_star,price,relation_uv,pay_buyer_count
ORDER BY price_star DESC
;
"

ads_goods_product="
WITH tmp AS (
    SELECT
        product_id
        ,content_type
        ,content_title
        ,relation_uv AS traffic_uv
        ,product_click_count AS click_cnt
        ,fan_click_count AS fan_clicks
        ,collect_guide_count AS collect_cnt
        ,cart_guide_items AS cart_items
        ,grass_purchase_amount AS sales
        ,grass_purchase_users AS pay_users
    FROM jtp_goods_warehouse.dwd_goods_info
    WHERE dt = '${data_date}'
)
INSERT OVERWRITE TABLE jtp_goods_warehouse.ads_goods_product
SELECT
     product_id
     ,content_type
     ,content_title
     ,SUM(traffic_uv) AS total_uv
     ,SUM(click_cnt) AS total_clicks
     ,SUM(fan_clicks) AS total_fan_clicks
     ,SUM(collect_cnt) AS total_collects
     ,SUM(cart_items) AS total_cart
     ,round(SUM(sales),2) AS total_sales
     ,SUM(pay_users) AS total_pay_users
     ,round(SUM(pay_users) / CAST(SUM(traffic_uv) AS DOUBLE),4) AS conversion_rate
FROM
    tmp
GROUP BY product_id, content_type, content_title
ORDER BY product_id, content_type, total_sales DESC
LIMIT 50;
"


ads_goods_used_profile="
WITH t1 AS (
    SELECT
        user_id
        ,product_id
        ,dt
        ,gender
        ,age
        ,price_preference
        ,interests
        ,CASE
            WHEN behavior_type = 'search' THEN 'search_user'
            WHEN behavior_type = 'visit' THEN 'visit_user'
            WHEN behavior_type = 'pay' THEN 'pay_user'
            ELSE 'unknown'
            END AS user_type
        ,CASE
            WHEN COUNT(user_id) OVER (PARTITION BY user_id ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) = 0
                THEN 'new'
            ELSE 'old'
            END AS customer_type
    FROM jtp_goods_warehouse.dwd_goods_info
    WHERE dt = '${data_date}'
)
INSERT OVERWRITE TABLE jtp_goods_warehouse.ads_goods_used_profile
SELECT
    user_type
    ,customer_type
    ,gender
    ,COUNT(DISTINCT user_id) AS user_count
    ,CASE
        WHEN age >= 18 AND age <= 24 THEN '年龄18-24'
        WHEN age >= 25 AND age <= 29 THEN '年龄25-29'
        WHEN age >= 30 AND age <= 34 THEN '年龄30-34'
        WHEN age >= 35 AND age <= 39 THEN '年龄35-39'
        WHEN age >= 40 AND age <= 49 THEN '年龄40-49'
        WHEN age >= 50 THEN '年龄5以上'
        ELSE '未知'
        END AS age_pu
    ,COUNT(DISTINCT CASE WHEN price_preference = '低' THEN user_id END) AS price_low
    ,COUNT(DISTINCT CASE WHEN price_preference = '中' THEN user_id END) AS price_mid
    ,COUNT(DISTINCT CASE WHEN price_preference = '高' THEN user_id END) AS price_high
    ,SUM(CASE WHEN interests LIKE '%美妆%' THEN 1 ELSE 0 END) AS interest_beauty
    ,SUM(CASE WHEN interests LIKE '%运动%' THEN 1 ELSE 0 END) AS interest_sport
    ,SUM(CASE WHEN interests LIKE '%音乐%' THEN 1 ELSE 0 END) AS interest_music
FROM t1
GROUP BY user_type, customer_type,gender,age
ORDER BY user_type, customer_type,gender,age;
"

ads_goods_word="
WITH t1 AS (
    SELECT
        dt
        ,sku_id
        ,sku_name=
        ,split(sku_name, '-') AS root_word_array
        ,product_click_count
        ,relation_uv
        ,pay_buyer_count
        ,grass_purchase_amount
    FROM jtp_goods_warehouse.dwd_goods_info
    WHERE dt = '${data_date}'
)
INSERT OVERWRITE TABLE jtp_goods_warehouse.ads_goods_word
SELECT
    dt
    ,root_word
    ,SUM(product_click_count) AS total_click
    ,SUM(relation_uv) AS total_traffic
    ,SUM(pay_buyer_count) AS total_pay_buyers
    ,SUM(grass_purchase_amount) AS total_sales
FROM t1
        LATERAL VIEW explode(root_word_array) root_words AS root_word
GROUP BY dt, root_word
ORDER BY dt, total_pay_buyers DESC
;

"

ads_goods_keywords="
WITH tmp AS (
    SELECT
        dt
        ,sku_id
        ,keyword_type
        ,keywords
        ,relation_uv AS traffic_uv
        ,product_click_count AS click_cnt
        ,pay_buyer_count AS pay_buyers
        ,grass_purchase_amount AS sales
    FROM jtp_goods_warehouse.dwd_goods_info
    WHERE dt = '${data_date}'
    AND keyword_type IS NOT NULL AND keyword_type IS NOT NULL
)
INSERT OVERWRITE TABLE jtp_goods_warehouse.ads_goods_keywords
SELECT
    keyword_type
    ,keywords
    ,SUM(traffic_uv) AS total_uv
    ,SUM(click_cnt) AS total_clicks
    ,SUM(pay_buyers) AS total_pay
    ,round(SUM(sales),2) AS total_sales
    ,round(SUM(pay_buyers) / CAST(SUM(traffic_uv) AS DOUBLE),4) AS conversion_rate
FROM tmp
GROUP BY keyword_type, keywords
ORDER BY keyword_type, conversion_rate DESC;
"

ads_goods_traffic_analysis="
INSERT OVERWRITE TABLE jtp_goods_warehouse.ads_goods_traffic_analysis
SELECT
    dt
    ,product_id
    ,traffic_source
    ,COUNT(DISTINCT user_id) AS visitor_count
    ,COUNT(collect_guide_count) AS collect_user_count
    ,COUNT(CASE WHEN behavior_type = 'cart' THEN user_id END) AS cart_user_count
    ,COUNT(CASE WHEN behavior_type = 'pay' THEN user_id END) AS pay_buyer_count
FROM jtp_goods_warehouse.dwd_goods_info
WHERE dt = '${data_date}'
GROUP BY traffic_source,product_id, dt
;
"


/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e  \
"${ads_goods_price_product}${ads_goods_product}${ads_goods_used_profile}
${ads_goods_word}${ads_goods_keywords}${ads_goods_traffic_analysis}"